/* @(#)GSAccountDAO.java    V0.1
 *
 * Project: GSdevelop
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   ------------ ---------- ---------------------------------------------------
 *   owen         2006-5-10
 *
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room, 
 *        Science & Technology Building, No. 9 South Street, Zhong Guan Cun, 
 *        Haidian District, Beijing ,100081, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with HiTRUST.
 *
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.hitrust.gs.bean.GSPayReceiveAbleBean;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSAccountMonthOfWorkingVO;
import com.hitrust.gs.vo.GSAccountMonthVO;
/**
 * 
 */
public class GSAccountDAO extends GSBaseDAO{
    /**
     * 查询本月对帐
     */
    public ArrayList queryAccountMonth(Connection aConn, GSAccountMonthVO aGSAccountMonthVO,ArrayList aOrg,int type) throws GSException {
        if(aOrg == null){
            aOrg =new ArrayList();
            String[] tTmpOrg ={"NoOrg"};
            aOrg.add(tTmpOrg);
        }
        GSAccountMonthOfWorkingVO tGSAccountMonthOfWorkingVO = new GSAccountMonthOfWorkingVO();
        GSProviderDAO tPDAO = new GSProviderDAO();
        String cczx = tPDAO.queryProvider(GSSystemConfigurator.getParam("gs.provider.cczxid")).getGs_provider_shortname();
        //系统当前时间
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String buf = df.format(new java.util.Date(System.currentTimeMillis()));
        
        StringBuffer tSql = new StringBuffer("select B.GS_DELIVER_GOODS_ID as P1,E.GS_ORG_NAME as P2,");
        
        tSql.append("F.GS_PROVIDER_SHORTNAME as P3,B.GS_CONCENTRICITY_ID as P4,C.GS_MANUFACTURE_NAME as P5,");
        tSql.append("C.GS_MANUFACTURE_SPEC as P6,C.GS_MANUFACTURE_TYPE as P7,C.GS_MANUFACTURE_BRAND as P8,");
        tSql.append("C.GS_MANUFACTURER_NAME as P9,B.GS_ORDER_COUNT as P10,B.GS_SIGNIN_COUNT as P11,");
        tSql.append("B.GS_GOODS_UNITPRICE as P12,B.GS_SIGNIN_DATE as P13,B.GS_SIGNIN_PERSON as P14,");
        tSql.append("B.GS_ORDER_TEMPID as P15,B.GS_ORDER_DETAIL_ID as P16,C.GS_QUANT_UNIT as P17, ");
        tSql.append("b.gs_manufacture_id as P18,c.GS_IS_TRADEFLAT_MANUFACTURE as P19 ");
        tSql.append("from GS_ORDER A,GS_ORDER_DETAIL B,GS_MANUFACTURE C,GS_ORDER_DEPT_INFO D,GS_ORGANIZATION E,GS_PROVIDER F");
        tSql.append(" where A.GS_ORDER_TEMPID=B.GS_ORDER_TEMPID and B.GS_CONCENTRICITY_ID=C.GS_CONCENTRICITY_ID");
        tSql.append(" and (b.gs_manufacture_id is null or (b.gs_manufacture_id is not null and b.gs_manufacture_id = c.gs_manufacture_id))");
        
        tSql.append(" and A.GS_WORKCORP_ID=D.GS_ORG_ID and A.GS_WORKCORP_ID=E.GS_ORG_ID");
        tSql.append(" and A.GS_DELIVER_GOODS_ADDR = D.GS_DELIVER_GOODS_ADDR and B.GS_PROVIDER_ID=F.GS_PROVIDER_ID");
        //是否已经纳入结算数据
        if(GSBaseConstants.TYPE_ORGA == type){//如果是运行公司端菜单"本月对帐/下载浏览"
        	tSql.append(" and B.GS_IS_BALANCEED='1'");        	
        }else{//如果是物资公司端菜单"供应商对帐/下载浏览"
        	tSql.append(" and B.GS_IS_PROV_BALANCEED='1'");
        }
        
        //取得年月
        String tYearMonth=aGSAccountMonthVO.year +aGSAccountMonthVO.month;
        //取得本月时间
        tSql.append(" and B.GS_BALANCE_MONTH='"+tYearMonth+"'");
        
        ArrayList tList = null;
        
        if (aGSAccountMonthVO.orderDeptId != null) {
            if (aGSAccountMonthVO.orderDeptId.equals("0000")) {
                if (aOrg.size() > 0) {
                    tSql.append(" and A.GS_WORKCORP_ID in(");
                    for (int i = 0; i < aOrg.size(); i++) {
                        String tStr[] = (String[]) aOrg.get(i);
                        if (i == aOrg.size() - 1) {
                            tSql.append("'" + tStr[0] + "')");
                        } else {
                            tSql.append("'" + tStr[0] + "',");
                        }
                    }
                }
            } else {
                tSql.append(" and A.GS_WORKCORP_ID ='" + aGSAccountMonthVO.orderDeptId + "'");
            }
        }
     
        if (!aGSAccountMonthVO.deliverGoodsId.equals("")) {
           tSql.append(" and B.GS_DELIVER_GOODS_ID like '%" + aGSAccountMonthVO.deliverGoodsId + "%'");
            }
        if (aGSAccountMonthVO.concentricityId != null||!aGSAccountMonthVO.concentricityId.equals("")) {
            tSql.append(" and B.GS_CONCENTRICITY_ID like '%" + aGSAccountMonthVO.concentricityId + "%'");
        }
       
        if (aGSAccountMonthVO.providerid != null||!aGSAccountMonthVO.providerid.equals("")) {
        	if(cczx.equals(aGSAccountMonthVO.providerid)){
        		tSql.append(" and (c.is_tradeflat_manufacture = 0 or F.GS_PROVIDER_SHORTNAME like '%" + aGSAccountMonthVO.providerid + "%')");
        	}else{
        		tSql.append(" and F.GS_PROVIDER_SHORTNAME like '%" + aGSAccountMonthVO.providerid + "%'");
        	}
        }
        if (aGSAccountMonthVO.manufInfo != null||!aGSAccountMonthVO.manufInfo.equals("")) {
            tSql.append(" and (C.GS_MANUFACTURE_NAME like '%" + aGSAccountMonthVO.manufInfo + "%'");
            tSql.append(" or C.GS_MANUFACTURE_TYPE like '%" + aGSAccountMonthVO.manufInfo + "%'");
            tSql.append(" or C.GS_MANUFACTURE_SPEC like '%" + aGSAccountMonthVO.manufInfo + "%'");
            tSql.append(" or C.GS_MANUFACTURE_BRAND like '%" + aGSAccountMonthVO.manufInfo + "%'");
            tSql.append(" or C.GS_MANUFACTURER_NAME like '%" + aGSAccountMonthVO.manufInfo + "%')");
        }
        
        if(!(aGSAccountMonthVO.startdate.equals("00000000")&&aGSAccountMonthVO.enddate.equals("99999999"))){
            tSql.append(" and to_char(to_date(B.GS_DELIVER_GOODS_DATE,'YYYY-MM-DD'),'YYYYMMDD') >='"
                + aGSAccountMonthVO.startdate + "'");
            tSql.append(" and to_char(to_date(B.GS_DELIVER_GOODS_DATE,'YYYY-MM-DD'),'YYYYMMDD') <='"
                   + aGSAccountMonthVO.enddate + "'");
        }
        if(GSBaseConstants.TYPE_ORGA == type){//如果是运行公司端菜单"本月对帐/下载浏览"
        	tSql.append(" order by F.GS_PROVIDER_ID,B.GS_DELIVER_GOODS_ID ");        	
        }else{//如果是物资公司端菜单"供应商对帐/下载浏览"
        	tSql.append(" order by A.GS_WORKCORP_ID,B.GS_DELIVER_GOODS_ID ");
        }
        
        
        GSLogWriter.debug(this, tSql.toString());
        Object[] tObj = {};
        try {
            tList = this.executeQuery(aConn, tGSAccountMonthOfWorkingVO, tSql.toString(), tObj);
        } catch (Exception e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tList;
    }
    
    
    /**
     * 显示本公司及下属单位应付款项信息列表
     * @return
     */
    public ArrayList queryPayAlert(Connection aConn,ArrayList aOrg)throws GSException{
        if(aOrg == null){
            aOrg =new ArrayList();
            String[] tTmpOrg ={"NoOrg"};
            aOrg.add(tTmpOrg);
        }
        ArrayList tList=new ArrayList();
        StringBuffer tSql = new StringBuffer("select * from GS_PAY_RECEIVE_ABLE ");
        tSql.append("where GS_WORKCORP_CATEGORY='1'");
        tSql.append("and GS_WORKCORP_ID in(");
        for (int i = 0; i < aOrg.size(); i++) {
            String tStr[] = (String[]) aOrg.get(i);
            if (i == aOrg.size() - 1) {
                tSql.append("'" + tStr[0] + "')");
            } else {
                tSql.append("'" + tStr[0] + "',");
            }
        }
        
        tSql.append(" order by GS_OPERATE_DATE desc");
        
        GSLogWriter.debug(this, tSql.toString());
        Object[] tObj = {};
        
        try{
            tList = this.executeQuery(aConn, new GSPayReceiveAbleBean(), tSql.toString(), tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tList;
    }
}
